import logging

import pandas as pd
import pymysql

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.float_format', '{:,}'.format)
pd.set_option('expand_frame_repr', False)

logging.basicConfig(level=logging.INFO,
                    format='%(asctime)s | %(filename)s - {%(lineno)d} - [%(levelname)s] - %(message)s',
                    datefmt='%Y-%m-%d %H:%M:%S', encoding='utf-8')

mysql_host = "127.0.0.1"
mysql_port = 3306
print("local host: {}, port: {}".format(mysql_host, mysql_port))

connect = pymysql.connect(host=mysql_host, port=mysql_port, user='root', passwd='hxsoft.net', database='stock')
cursor = connect.cursor()

cursor.execute("select stock_code from stock.stock_code")
rows = cursor.fetchall()
for row in rows:
    stock_code = row[0]
    cursor.execute(
        "select * from stock_details sd where sd.stock_code = '{}' order by sd.stock_date asc".format(stock_code))
    df = pd.DataFrame(list(cursor.fetchall()))

    ## zl
    df["zz"] = (df[2] + df[5] + df[4]) / 3
    df["zl1"] = df["zz"].ewm(span=8, adjust=True).mean()
    df["zl2"] = df["zz"].ewm(span=55, adjust=True).mean()

    ## vol
    df["v5"] = df[3].rolling(5).mean()
    df["v20"] = df[3].rolling(20).mean()

    ## macd
    df["s_ma"] = df[3].ewm(span=12, adjust=True).mean()
    df["l_ma"] = df[3].ewm(span=26, adjust=True).mean()
    df["dif"] = df["s_ma"] - df["l_ma"]
    df["dea"] = df["dif"].ewm(span=9, adjust=True).mean()
    df["macd"] = (df["dif"] - df["dea"]) * 2

    for i in range(len(df)):
        date = df.loc[i][1]
        zl1 = df.loc[i]["zl1"]
        zl2 = df.loc[i]["zl2"]
        v5 = df.loc[i]["v5"]
        v5 = -1 if v5 != v5 else v5
        v20 = df.loc[i]["v20"]
        v20 = -1 if v20 != v20 else v20
        dif = df.loc[i]["dif"]
        dif = -1 if dif != dif else dif
        dea = df.loc[i]["dea"]
        dea = -1 if dea != dea else dea
        macd = df.loc[i]["macd"]
        macd = -1 if macd != macd else macd

        cursor.execute(
            "select count(1) from stock_value where stock_code = '{}' and stock_date = '{}'".format(stock_code, date))

        count_row = cursor.fetchone()
        if count_row[0] == 0:
            cursor.execute(
                "insert into stock_value values ('{}','{}',{},{},{},{},{},{},{})".format(stock_code, date, zl1, zl2, v5,
                                                                                         v20, dif, dea, macd))
        else:
            cursor.execute(
                "update stock_value set zl1 = {}, zl2 = {}, v5 = {},v20 = {},dif = {},dea = {},macd = {} where stock_code = '{}' and stock_date = '{}'".format(
                    zl1, zl2, v5, v20, dif, dea, macd, stock_code, date))
        connect.commit()

    print("stock_code: {} is finished".format(stock_code))

cursor.close()
connect.close()
